package DBLayer;
import ModelLayer.ProductAttribute;
import java.sql.*;
import java.util.ArrayList;

public class DBProductAttribute {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBProductAttribute() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public ProductAttribute getProductAttributeByID(int id) {
        return selectSingle("SELECT `id_product_attribute`, `id_product`, `ean13`, `ecotax`, `quantity`, `price`, `wholesale_price`, `reference`, `supplier_reference`, `location`, `weight`, `default_on` FROM `ps_product_attribute` WHERE `id_product_attribute` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<ProductAttribute> getProductAttributeList() {
        return selectMultiple("SELECT `id_product_attribute`, `id_product`, `ean13`, `ecotax`, `quantity`, `price`, `wholesale_price`, `reference`, `supplier_reference`, `location`, `weight`, `default_on` FROM `ps_product_attribute` ORDER BY `id_product_attribute` ASC;");
    }

    public ArrayList<Integer> getProductAttributeIDsByAttributeID(int id) {
        return selectIDs("SELECT `id_product_attribute` FROM `ps_product_attribute_combination` WHERE `id_attribute` = " + id + " ORDER BY `id_product_attribute` ASC;");
    }

    private ProductAttribute selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ProductAttribute productAttribute = new ProductAttribute();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                productAttribute = buildProductAttribute(result);
            }
            System.out.println("Fetching ProductAttribute Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return productAttribute;
    }

    private ArrayList<ProductAttribute> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<ProductAttribute> productAttributes = new ArrayList<ProductAttribute>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                ProductAttribute productAttribute = new ProductAttribute();
                productAttribute = buildProductAttribute(result);
                productAttributes.add(productAttribute);
            }
            System.out.println("Fetching ProductAttribute List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return productAttributes;
    }

   private ArrayList<Integer> selectIDs(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Integer> ids = new ArrayList<Integer>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                ids.add(result.getInt("id_product_attribute"));
            }
            System.out.println("Fetching ProductAttribute ID List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return ids;
    }

   private ProductAttribute buildProductAttribute(ResultSet result) {
        DBAttribute dbAttribute = new DBAttribute();
        ProductAttribute productAttribute = new ProductAttribute();
        try {
            productAttribute.setIDProductAttribute(result.getInt("id_product_attribute"));
            productAttribute.setIDProduct(result.getInt("id_product"));
            productAttribute.setReference(result.getString("reference"));
            productAttribute.setSupplierReference(result.getString("supplier_reference"));
            productAttribute.setLocation(result.getString("location"));
            productAttribute.setEan13(result.getString("ean13"));
            productAttribute.setWholesalePrice(result.getFloat("wholesale_price"));
            productAttribute.setPrice(result.getFloat("price"));
            productAttribute.setEcotax(result.getFloat("ecotax"));
            productAttribute.setQuantity(result.getInt("quantity"));
            productAttribute.setWeight(result.getFloat("weight"));
            if (result.getInt("default_on") == 1) {
                productAttribute.setDefaultOn(true);
            } else {
                productAttribute.setDefaultOn(false);
            }
            productAttribute.setAttributeIDs(dbAttribute.getAttributeIDsByProductAttributeID(result.getInt("id_product_attribute")));

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return productAttribute;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addProductAttribute(ProductAttribute productAttribute) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_product_attribute` (`id_product_attribute`, `id_product`, `reference`, `supplier_reference`, `location`, `ean13`, `wholesale_price`, `price`, `ecotax`, `quantity`, `weight`, `default_on`) "
                + "VALUES (" + productAttribute.getIDProductAttribute() + ", " + productAttribute.getIDProduct() + ", '" + productAttribute.getReference() + "', '" + productAttribute.getSupplierReference() + "', '" + productAttribute.getLocation() + "', '" + productAttribute.getEan13() + "', " + productAttribute.getWholesalePrice() + ", " + productAttribute.getPrice() + ", " + productAttribute.getEcotax() + ", " + productAttribute.getQuantity() + ", " + productAttribute.getWeight() + ", ";
        if (productAttribute.getDefaultOn() == true)    { query += "1);"; }
        else                                            { query += "0);"; }
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added ProductAttribute ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateProductAttribute(ProductAttribute productAttribute, int id) {
        int returnID = 0;
        Statement stmt = null;
	String query = "UPDATE `ps_product_attribute` SET "
                + "`id_product_attribute`   = "  + productAttribute.getIDProductAttribute() + ", "
                + "`id_product`             = "  + productAttribute.getIDProduct() + ", "
                + "`reference`              = '" + productAttribute.getReference() + "', "
                + "`supplier_reference`     = '" + productAttribute.getSupplierReference() + "', "
                + "`location`               = '" + productAttribute.getLocation() + "', "
                + "`ean13`                  = '" + productAttribute.getEan13() + "', "
                + "`wholesale_price`        = "  + productAttribute.getWholesalePrice() + ", "
                + "`price`                  = "  + productAttribute.getPrice() + ", "
                + "`ecotax`                 = "  + productAttribute.getEcotax() + ", "
                + "`quantity`               = "  + productAttribute.getQuantity() + ", "
                + "`weight`                 = "  + productAttribute.getWeight() + ", ";
        if (productAttribute.getDefaultOn() == true)    { query += "`default_on` = 1 "; }
        else                                            { query += "`default_on` = 0 "; }
        query += "WHERE `id_product_attribute` = " + id + " LIMIT 1; ";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = productAttribute.getIDProductAttribute();
            System.out.println("Updated ProductAttribute ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteProductAttribute(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_product_attribute` WHERE `id_product_attribute` = " + id + " LIMIT 1;");
            System.out.println("Deleted ProductAttribute ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
